package com.poi.action;

import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;
import org.apache.struts2.ServletActionContext;
import tree_util.Account;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class ExportAction extends ActionSupport  {
	
	private static final long serialVersionUID = 1L;
    private DbBean db = new DbBean();
    private String sql = null;  

	private String contentType;
	private long contentLength;
	private String contentDisposition;
	private InputStream inputStream;
	
	public String getContentType(){
		return contentType;		
	}
	public long getContentLength(){
		return contentLength;
	}
	public String getContentDisposition(){
		return contentDisposition;
	}
	public InputStream getInputStream(){
		return inputStream;
	}
	
	
	public static int size;
	public String filename;
	 private javax.servlet.http.HttpServletResponse response;

	public String getFilename() {
		return filename;
	}

	public void setFilename(String filename) {
		this.filename = filename;
	}

	public String downloadexcel()throws Exception{
		contentType = "text/xls";
		ServletContext servletContext = ServletActionContext.getServletContext();
		String upsql = selecttable.ctable;
		System.out.println(upsql);
		String excelName=null;
		if(upsql.equals("DominantZooplankton")){
			contentDisposition = "attachment;filename=fuyoudongwujiancebiao.xls";
			excelName = servletContext.getRealPath("/xmlguize/muban/fuyoudongwujiancebiao.xls");
		}
		if(upsql.equals("DominantPhytoplankton")){
			contentDisposition = "attachment;filename=fuyouzhiwujiancebiao.xls";
			excelName = servletContext.getRealPath("/xmlguize/muban/fuyouzhiwujiancebiao.xls");
		}
		if(upsql.equals("DominantBenthos")){
			contentDisposition = "attachment;filename=diqishengwujiancebiao.xls";
			excelName = servletContext.getRealPath("/xmlguize/muban/diqishengwujiancebiao.xls");
		}
		if(upsql.equals("Sediment")){
			contentDisposition = "attachment;filename=chenjiwujiancebiao.xls";
			excelName = servletContext.getRealPath("/xmlguize/muban/chenjiwujiancebiao.xls");
		}
		if(upsql.equals("Fishes")){
			contentDisposition = "attachment;filename=yuyangbenjiancebiao.xls";
			excelName = servletContext.getRealPath("/xmlguize/muban/yuyangbenjiancebiao.xls");
		}
		if(upsql.equals("FishEggs")){
			contentDisposition = "attachment;filename=luanyangbenjiancebiao.xls";
			excelName = servletContext.getRealPath("/xmlguize/muban/luanyangbenjiancebiao.xls");
		}
		if(upsql.equals("CatchTools")){
			contentDisposition = "attachment;filename=wangjujiancebiao.xls";
			excelName = servletContext.getRealPath("/xmlguize/muban/wangjujiancebiao.xls");
		}
		inputStream = new FileInputStream(excelName);
		contentLength=inputStream.available();

		return SUCCESS;
	}


	 public String exportexcel(){
		 System.out.println("11111111111");
			Account account=(Account) ServletActionContext.getRequest().getSession().getAttribute("account");
			String userid = account.getID();
			System.out.println(userid);
	        try {
	            HttpServletResponse response = ServletActionContext.getResponse();
	            response.setContentType("octets/stream");
	            SimpleDateFormat format=new SimpleDateFormat("yyyyMMddHHmmss");
	            String str = new SimpleDateFormat("yyyyMMddHHmmss").format(new java.util.Date())+"excel";
	            response.addHeader("Content-Disposition","attachment;filename="+str+".xls");  
	            OutputStream out = response.getOutputStream(); 
	            Map mapping=ActionContext.getContext().getSession();
	            //此处为模拟数据，实际应用从数据库中查出  
	            List<String> result = new ArrayList<String>();  
	            LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();  	            	           
	        	Connection conn = db.getConnection();
	        	String upsql = selecttable.ctable;
	        	if(upsql.equals("Sediment")){
	        		ArrayList<String> ss = new ArrayList<String>();
	        	String sql = "select " +
	        			"MonitoringSite.Investigator," +
	        			"MonitoringSite.River," +
	        			"MonitoringSite.Site," +
	        			"MonitoringSite.InvestigationDate," +
	        			"MonitoringSite.StartLongitude," +
	        			"MonitoringSite.StartLatitude," +
	        			"MonitoringSite.Weather, " +
	        			"MonitoringSite.Temperature," +
	        			"FractureSurface.Position," +
	        			"FractureSurface.Distance2Bank," +
	        			"Sediment.Photo from MonitoringSite," +
	        			"FractureSurface," +
	        			"FractureSurface_Sediment," +
	        			"Sediment " +
	        			"where " +
	        			"FractureSurface_Sediment.ID_Sediment=Sediment.SampleID " +
	        			"and FractureSurface_Sediment.ID_FractureSurface = FractureSurface.ID " +
	        			"and FractureSurface.ID_MonitoringSite=MonitoringSite.InverstigationID " +
	        			"and MonitoringSite.user_id='"+userid+"';";
	            try {
	            	  
	    			ResultSet rs = db.executeQuery(sql);
	    			while(rs.next()){
	    				for(int i=1;i<=11;i++){ss.add(rs.getString(i));}
	    				
	    			}

	    			
	    		} catch (SQLException e) {
	    			// TODO Auto-generated catch block
	    			e.printStackTrace();
	    		}
	            
	       
	            String[] headers = {"监测人员", "水域", "地点","日期", "经度", "纬度","天气", "气温", "断面","距岸距离", "沉积物照片"};  

	            mapping.put("data",ss);
	        	System.out.println("headers.length==========="+headers.length);
	        	ArrayList<String> content = (ArrayList<String>)mapping.get("data");  
	       
	            ExportExcelUtils.exportExcel("沉积物监测表",  headers, content, out, "");  

	            out.close();  
	        	}
	        	
	        	if(upsql.equals("DominantZooplankton")){
	        		ArrayList<String> ss = new ArrayList<String>();
		        	String sql = "select " +
		        			"MonitoringSite.Investigator," +
		        			"MonitoringSite.River," +
		        			"MonitoringSite.Site," +
		        			"MonitoringSite.InvestigationDate," +
		        			"MonitoringSite.StartLongitude," +
		        			"MonitoringSite.StartLatitude," +
		        			"MonitoringSite.Weather, " +
		        			"MonitoringSite.Temperature," +
		        			"FractureSurface.Position," +
		        			"FractureSurface.Distance2Bank," +
		        			"DominantZooplanktonSpecies.Name," +
		        			"DominantZooplanktonSpecies.Quality," +
		        			"DominantZooplanktonSpecies.Biomass " +
		        			"from " +
		        			"MonitoringSite," +
		        			"FractureSurface," +
		        			"Zooplankton," +
		        			"DominantZooplanktonSpecies " +
		        			"where " +
		        			"MonitoringSite.InverstigationID=FractureSurface.ID_MonitoringSite " +
		        			"and FractureSurface.ID=Zooplankton.ID_FractureSurface " +
		        			"and Zooplankton.SampleID=DominantZooplanktonSpecies.ID_Zooplankton " +
		        			"and MonitoringSite.user_id='"+userid+"';";
		            try {
		    			ResultSet rs = db.executeQuery(sql);
		    			while(rs.next()){
		    			  
		    				for(int i=1;i<=13;i++){ss.add(rs.getString(i));}
		    			}

		    			
		    		} catch (SQLException e) {
		    			// TODO Auto-generated catch block
		    			e.printStackTrace();
		    		}
		            
		       
		            String[] headers = {"监测人员", "水域", "地点","日期", "经度", "纬度","天气", "气温", "断面","距岸距离", "浮游动物名称","数量", "生物量"};  
		            mapping.put("data",ss);
		        	System.out.println("headers.length==========="+headers.length);
		        	ArrayList<String> content = (ArrayList<String>)mapping.get("data");  
		        
		            System.out.println("result======"+result);
		            ExportExcelUtils.exportExcel("浮游动物监测表",  headers, content, out, "");  

		            out.close();  
		        	}
		        	
	        	if(upsql.equals("DominantPhytoplankton")){
	        		ArrayList<String> ss = new ArrayList<String>();
		        	String sql = "select " +
		        			"MonitoringSite.Investigator," +
		        			"MonitoringSite.River," +
		        			"MonitoringSite.Site," +
		        			"MonitoringSite.InvestigationDate," +
		        			"MonitoringSite.StartLongitude," +
		        			"MonitoringSite.StartLatitude," +
		        			"MonitoringSite.Weather, " +
		        			"MonitoringSite.Temperature," +
		        			"FractureSurface.Position," +
		        			"FractureSurface.Distance2Bank," +
		        			"DominantPhytoplanktonSpecies.Name," +
		        			"DominantPhytoplanktonSpecies.Quality," +
		        			"DominantPhytoplanktonSpecies.Biomass " +
		        			"from " +
		        			"MonitoringSite," +
		        			"FractureSurface," +
		        			"Phytoplankton," +
		        			"DominantPhytoplanktonSpecies " +
		        			"where " +
		        			"MonitoringSite.InverstigationID=FractureSurface.ID_MonitoringSite " +
		        			"and FractureSurface.ID=Phytoplankton.ID_FractureSurface " +
		        			"and Phytoplankton.SampleID=DominantPhytoplanktonSpecies.ID_Phytoplankton " +
		        			"and MonitoringSite.user_id='"+userid+"';";
		            try {
		    			ResultSet rs = db.executeQuery(sql);
		    			while(rs.next()){
		    				while(rs.next()){
				    			  
			    				for(int i=1;i<=13;i++){ss.add(rs.getString(i));}
			    			}
		    				
		    				
		    			}

		    			
		    		} catch (SQLException e) {
		    			// TODO Auto-generated catch block
		    			e.printStackTrace();
		    		}
		            
		       
		            String[] headers = {"监测人员", "水域", "地点","日期", "经度", "纬度","天气", "气温", "断面","距岸距离", "浮游植物名称","数量", "生物量"};  
		            mapping.put("data",ss);
		        	System.out.println("headers.length==========="+headers.length);
		        	ArrayList<String> content = (ArrayList<String>)mapping.get("data");  
		        
		            ExportExcelUtils.exportExcel("浮游植物监测表",  headers, content, out, "");  

		            out.close();  
		        	}
		        	
	        	if(upsql.equals("DominantBenthos")){
	        		ArrayList<String> ss = new ArrayList<String>();
		        	String sql = "select " +
		        			"MonitoringSite.Investigator," +
		        			"MonitoringSite.River," +
		        			"MonitoringSite.Site," +
		        			"MonitoringSite.InvestigationDate," +
		        			"MonitoringSite.StartLongitude," +
		        			"MonitoringSite.StartLatitude," +
		        			"MonitoringSite.Weather, " +
		        			"MonitoringSite.Temperature," +
		        			"FractureSurface.Position," +
		        			"FractureSurface.Distance2Bank," +
		        			"DominantBenthosSpecies.Name," +
		        			"DominantBenthosSpecies.Quality," +
		        			"DominantBenthosSpecies.Biomass " +
		        			"from MonitoringSite," +
		        			"FractureSurface," +
		        			"Benthos," +
		        			"DominantBenthosSpecies " +
		        			"where " +
		        			"MonitoringSite.InverstigationID=FractureSurface.ID_MonitoringSite " +
		        			"and FractureSurface.ID=Benthos.ID_FractureSurface " +
		        			"and Benthos.SampleID=DominantBenthosSpecies.ID_Benthos " +
		        			"and MonitoringSite.user_id='"+userid+"';";
		            try {
		    			ResultSet rs = db.executeQuery(sql);
		    			while(rs.next()){
		    				while(rs.next()){
				    			  
			    				for(int i=1;i<=13;i++){ss.add(rs.getString(i));}
			    			}
		    			}

		    			
		    		} catch (SQLException e) {
		    			// TODO Auto-generated catch block
		    			e.printStackTrace();
		    		}
		            
		       
		            String[] headers = {"监测人员", "水域", "地点","日期", "经度", "纬度","天气", "气温", "断面","距岸距离", "底栖生物名称","数量", "生物量"};  

		            mapping.put("data",ss);
		        	System.out.println("headers.length==========="+headers.length);
		        	ArrayList<String> content = (ArrayList<String>)mapping.get("data");  
		        
		            ExportExcelUtils.exportExcel("底栖生物监测表",  headers, content, out, "");  

		            out.close();  
		        	}
		        	
	        	if(upsql.equals("Fishes")){
	        		ArrayList<String> ss = new ArrayList<String>();
		        	String sql = "select "+ 
		        					"MonitoringSite.Investigator, "+
		        					"MonitoringSite.River, " +
		        					"MonitoringSite.Site, "+
		        					"MonitoringSite.InvestigationDate, "+
		        					"MonitoringSite.StartLongitude, "+
		        					"MonitoringSite.StartLatitude, "+
		        					"MonitoringSite.Weather, "+
		        					"MonitoringSite.Temperature, "+
		        					"FractureSurface.Position, "+
		        					"FractureSurface.Distance2Bank, "+
		        					"WaterLayer.Layer, "+
		        					"WaterLayer.Depth, "+
		        					"WaterLayer.Temperature, "+
		        					"WaterLayer.WaterLevel, "+
		        					"WaterLayer.Velocity, "+
		        					"Catches.Name, "+
		        					"Fishes.BodyLength, "+
		        					"Fishes.Length, "+
		        					"Fishes.BodyWeight, "+
		        					"Fishes.Age "+
		        					"from "+ 
		        					"MonitoringSite, "+
		        					"FractureSurface, "+
		        					"WaterLayer, "+
		        					"Catches, "+
		        					"Fishes, "+
		        					"MeasuringPoint, "+
		        					"MeasuringLine "+
		        					"where "+ 
		        					"MonitoringSite.InverstigationID=FractureSurface.ID_MonitoringSite "+ 
		        					"and FractureSurface.ID=MeasuringLine.ID_FractureSurface "+ 
		        					"and MeasuringLine.ID=MeasuringPoint.ID_MeasuringLine "+
		        					"and MeasuringPoint.ID=WaterLayer.ID_MeasuringPoint "+
		        					"and WaterLayer.ID=Catches.ID_WaterLayer "+
		        					"and Catches.SampleID=Fishes.ID_Catches " +
		        					"and MonitoringSite.user_id='"+userid+"';";
		            try {
		    			ResultSet rs = db.executeQuery(sql);
		    			while(rs.next()){
		    				while(rs.next()){
				    			  
			    				for(int i=1;i<=20;i++){ss.add(rs.getString(i));}
			    			}
		    				
		    			}

		    			
		    		} catch (SQLException e) {
		    			// TODO Auto-generated catch block
		    			e.printStackTrace();
		    		}
		            
		       
		            String[] headers = {"监测人员", "水域", "地点","日期", "经度", "纬度","天气", "气温", "断面","距岸距离", "采样水层","深度", "水温", "水位","流量", "鱼类名称", "体长","全长", "体重", "年龄"};  
		            mapping.put("data",ss);
		        	System.out.println("headers.length==========="+headers.length);
		        	ArrayList<String> content = (ArrayList<String>)mapping.get("data");  
		        
		            ExportExcelUtils.exportExcel("鱼样本监测表",  headers, content, out, "");  

		            out.close();  
		        	}
		        	
	        	
	        	if(upsql.equals("FishEggs")){
	        		ArrayList<String> ss = new ArrayList<String>();
		        	String sql = "select "+ 
		        					"MonitoringSite.Investigator,"+
		        					"MonitoringSite.River, "+
		        					"MonitoringSite.Site, "+
		        					"MonitoringSite.InvestigationDate, "+
		        					"MonitoringSite.StartLongitude, "+
		        					"MonitoringSite.StartLatitude, "+
		        					"MonitoringSite.Weather, "+
		        					"MonitoringSite.Temperature, "+
		        					"FractureSurface.Position, "+
		        					"FractureSurface.Distance2Bank, "+
		        					"WaterLayer.Layer, "+
		        					"WaterLayer.Depth, "+
		        					"WaterLayer.Temperature, "+
		        					"WaterLayer.WaterLevel, "+
		        					"WaterLayer.Velocity, "+
		        					"Catches.Name, "+
		        					"Catches.Name, "+
		        					"FishEggs.Period, "+
		        					"FishEggs.Diameter, "+
		        					"FishEggs.EMDiameter, "+
		        					"FishEggs.PigmentProp, "+
		        					"FishEggs.EmbryoProp "+
		        					"from "+ 
		        					"MonitoringSite, "+
		        					"FractureSurface, "+
		        					"WaterLayer, "+
		        					"Catches, "+
		        					"Fishes, "+
		        					"MeasuringPoint, "+
		        					"MeasuringLine "+
		        					"where "+ 
		        					"MonitoringSite.InverstigationID=FractureSurface.ID_MonitoringSite "+ 
		        					"and FractureSurface.ID=MeasuringLine.ID_FractureSurface "+ 
		        					"and MeasuringLine.ID=MeasuringPoint.ID_MeasuringLine "+
		        					"and MeasuringPoint.ID=WaterLayer.ID_MeasuringPoint "+
		        					"and WaterLayer.ID=Catches.ID_WaterLayer "+
		        					"and Catches.SampleID=FishEggs.ID_Catches " +
		        					"and MonitoringSite.user_id='"+userid+"';";
		            try {
		    			ResultSet rs = db.executeQuery(sql);
		    			while(rs.next()){
		    				while(rs.next()){
				    			  
			    				for(int i=1;i<=19;i++){ss.add(rs.getString(i));}
			    			}
		    				
		    			}

		    			
		    		} catch (SQLException e) {
		    			// TODO Auto-generated catch block
		    			e.printStackTrace();
		    		}
		            
		       
		            String[] headers = {"监测人员", "水域", "地点","日期", "经度", "纬度","天气", "气温", "断面","距岸距离", "采样水层","深度", "水温", "水位","流量", "鱼类名称", "发育期","卵径", "卵膜径", "色素性状", "胚胎性状"};  
		            mapping.put("data",ss);
		        	System.out.println("headers.length==========="+headers.length);
		        	ArrayList<String> content = (ArrayList<String>)mapping.get("data");  
		        
		            ExportExcelUtils.exportExcel("卵样本监测表",  headers, content, out, "");  

		            out.close();  
		        	}
		        	
	        	
	        	if(upsql.equals("CatchTools")){
	        		ArrayList<String> ss = new ArrayList<String>();
		        	String sql = "select "+ 
		        			"MonitoringSite.Investigator, "+
		        			"MonitoringSite.River, " +
		        			"MonitoringSite.Site, "+
		        			"MonitoringSite.InvestigationDate, "+
		        			"MonitoringSite.StartLongitude, "+
		        			"MonitoringSite.StartLatitude, "+
		        			"MonitoringSite.Weather, "+
		        			"MonitoringSite.Temperature, "+
		        			"FractureSurface.Position, "+
		        			"FractureSurface.Distance2Bank, "+
		        			"WaterLayer.Layer, "+
		        			"WaterLayer.Depth, "+
		        			"WaterLayer.Temperature, "+
		        			"WaterLayer.WaterLevel, "+
		        			"WaterLayer.Velocity, "+
		        			"CatchTools.Name, "+
		        			"CatchTools.NetsModel, "+
		        			"CatchTools.NetMouthArea, "+
		        			"CatchTools.NetMouthDip, "+
		        			"CatchTools.NetMouthVelocity "+
		        			"from "+ 
		        			"MonitoringSite, "+
		        			"FractureSurface, "+
		        			"WaterLayer, "+
		        			"CatchTools, "+
		        			"WaterLayer_CatchTools, "+
		        			"MeasuringPoint, "+
		        			"MeasuringLine "+
		        			"where "+ 
		        			"MonitoringSite.InverstigationID=FractureSurface.ID_MonitoringSite "+ 
		        			"and FractureSurface.ID=MeasuringLine.ID_FractureSurface "+ 
		        			"and MeasuringLine.ID=MeasuringPoint.ID_MeasuringLine "+
		        			"and MeasuringPoint.ID=WaterLayer.ID_MeasuringPoint "+
		        			"and WaterLayer.ID= WaterLayer_CatchTools.ID_WaterLayer "+
		        			"and CatchTools.SampleID= WaterLayer_CatchTools.ID_CatchTools " +
		        			"and MonitoringSite.user_id='"+userid+"';";
		            try {
		    			ResultSet rs = db.executeQuery(sql);
		    			while(rs.next()){
		    				while(rs.next()){
				    			  
			    				for(int i=1;i<=20;i++){ss.add(rs.getString(i));}
			    			}
		    	           
		    			}

		    			
		    		} catch (SQLException e) {
		    			// TODO Auto-generated catch block
		    			e.printStackTrace();
		    		}
		            
		       
		            String[] headers = {"监测人员", "水域", "地点","日期", "经度", "纬度","天气", "气温", "断面","距岸距离", "采样水层","深度", "水温", "水位","流量", "网具名称", "网型","网口面积", "网口倾角", "网口流速"};  

		            mapping.put("data",ss);
		        	System.out.println("headers.length==========="+headers.length);
		        	ArrayList<String> content = (ArrayList<String>)mapping.get("data");  
		        
		            ExportExcelUtils.exportExcel("网具监测表",  headers, content, out, "");  

		            out.close();  
		        	}
		        	
	        	
	        	
	        	
	        } catch (Exception e) {  

	            e.printStackTrace();  

	        }  

	       System.out.println("dsfsadf");
	        return null;  
	        
	    }  


}
